/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package br.uniceub.fsw.ativos.dao;
import br.uniceub.fsw.ativos.vo.*;
import java.sql.ResultSet;
import java.util.ArrayList;
/**
 *
 * @author markaum
 */
public class CategoriaDAO {
    private BD bd;

    public CategoriaDAO () {
        bd=new BD();
    }
/*
    public static void main(String[] arags){
        CategoriaDAO dao=new CategoriaDAO();
        ArrayList a=dao.consultarSubCategoria(new Integer(2));
        for (int i=0; i<a.size(); i++){
            CategoriaVO vo=(CategoriaVO) a.get(i);
            System.out.println(vo.getNoCategoria());
        }
    }
 */
     public boolean salvar(CategoriaVO cat) {
        String SQL ;
        if(cat.getCdCategoriaPai()==0){
            //cat.setCdCategoriaPai(null);
            //SQL = SQL.replaceAll("<01>", null);
            SQL= "INSERT INTO tb_categoria (NO_Categoria,DS_Categoria) VALUES ('<02>','<03>')";
            SQL = SQL.replaceAll("<02>", cat.getNoCategoria());
            SQL = SQL.replaceAll("<03>", cat.getDsCategoria());
            
        }
        else{
            SQL= "INSERT INTO tb_categoria (CD_CategoriaPai, NO_Categoria,DS_Categoria) VALUES (<01>,'<02>','<03>')";
            SQL = SQL.replaceAll("<01>", cat.getCdCategoriaPai().toString());
            SQL = SQL.replaceAll("<02>", cat.getNoCategoria());
            SQL = SQL.replaceAll("<03>", cat.getDsCategoria());
        }
        

        return bd.execSQL(SQL);
    }
     public boolean apagar(CategoriaVO cat){
        String SQL="DELETE FROM tb_categoria WHERE ID_Categoria=<01>";
        SQL=SQL.replaceAll("<01>", cat.getIdCategoria().toString());
        return bd.execSQL(SQL);
    }
    public ArrayList consultarSubCategoria(){
        String SQL="SELECT TP.ID_Categoria,TF.ID_Categoria,tp.NO_Categoria,"+
                   "tf.NO_Categoria,tf.DS_Categoria FROM TB_Categoria TP,"+
                   "TB_Categoria TF WHERE TF.CD_CategoriaPai = TP.ID_Categoria";

        ArrayList aList=new ArrayList();
        try{
            ResultSet rs=bd.getResultSet(SQL);
            while (rs.next()){
                CategoriaVO p=new CategoriaVO();
                p.setIdCategoria(new Integer(rs.getInt("TF.ID_Categoria")));
                p.setNoSubCategoria(rs.getString("TF.NO_Categoria"));
                p.setNoCategoria(rs.getString("TP.NO_Categoria"));
                p.setCdCategoriaPai(new Integer(rs.getInt("TP.ID_Categoria")));
                p.setDsCategoria(rs.getString("TF.DS_Categoria"));
                aList.add(p);
            }
            rs.close();
        } catch(Exception ex){
            System.out.println("Ativo.consultaPorNome:" + ex.getMessage());
        }
        return aList;
    }


    public ArrayList consultarTodas(){
        String SQL="SELECT * FROM tb_categoria WHERE CD_CategoriaPai IS NULL";

        ArrayList aList=new ArrayList();
        try{
            ResultSet rs=bd.getResultSet(SQL);
            while (rs.next()){
                CategoriaVO p=new CategoriaVO();
                p.setIdCategoria(new Integer(rs.getInt("ID_Categoria")));
                p.setNoCategoria(rs.getString("NO_Categoria"));
                p.setCdCategoriaPai(new Integer(rs.getInt("CD_CategoriaPai")));
                p.setDsCategoria(rs.getString("DS_Categoria"));
                aList.add(p);
            }
            rs.close();
        } catch(Exception ex){
            System.out.println("Ativo.consultaPorNome:" + ex.getMessage());
        }
        return aList;
    }

    public ArrayList consultarTodasId(Integer id){
        String SQL="SELECT * FROM tb_categoria WHERE ID_Categoria = "+ id;
        ArrayList aList=new ArrayList();
        try{
            ResultSet rs=bd.getResultSet(SQL);
            while (rs.next()){
                CategoriaVO p=new CategoriaVO();
                p.setIdCategoria(new Integer(rs.getInt("ID_Categoria")));
                p.setNoCategoria(rs.getString("NO_Categoria"));
                p.setCdCategoriaPai(new Integer(rs.getInt("CD_CategoriaPai")));
                p.setDsCategoria(rs.getString("DS_Categoria"));
                aList.add(p);
            }
            rs.close();
        } catch(Exception ex){
            System.out.println("Ativo.consultaPorNome:" + ex.getMessage());
        }
        return aList;
    }

}